package ext.tianma.bom.eco;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import wt.change2.WTChangeOrder2;
import wt.fc.PersistenceHelper;
import wt.log4j.LogR;
import wt.part.WTPart;
import wt.util.WTException;
import wt.util.WTProperties;
import wt.vc.VersionControlHelper;
import ext.com.core.CoreUtil;
import ext.tianma.bom.bean.BOMBean;
import ext.tianma.bom.util.GenericUtil;
import ext.tianma.util.PropertiesUtil;

/**
 * ECO变更
 * 
 * @author gaojinwang
 * 
 */
public class ECOHelp {

	private static final Logger log = LogR.getLogger(ECOHelp.class.getName());

	public static String configFilePath = "codebase" + File.separator + "ext" + File.separator + "tianma"
			+ File.separator + "cfg" + File.separator + "TianmaPro.properties";

	/**
	 * 正确返回格式为： { tmCode:"000", dataList:[{
	 * number:"E001",view:"A10",state:"正在工作"}, {
	 * number:"E001",view:"A10",state:"正在工作"} ] length:2 }
	 * 
	 * @param oid
	 * @return
	 */
	public static String queryECOHistory(String oid) {
		JSONObject object = new JSONObject();
		JSONArray array = new JSONArray();
		List<BOMBean> list = new ArrayList<BOMBean>();
		try {
			WTChangeOrder2 eco = (WTChangeOrder2) BOMHelp.REF_FACTORY.getReference(oid).getObject();

			if (eco != null) {
				list = ECOCRUDUtil.queryEffectPartByECONumber(eco.getNumber(), "0");
				for (BOMBean bean : list) {
					JSONObject obj = new JSONObject();
					WTPart part = BOMHelp.getPart(bean.getEffectnumber(), "", bean.getView());
					if (part != null) {
						String state = part.getState().getState().getDisplay(Locale.CHINA);
						String version = VersionControlHelper.getIterationDisplayIdentifier(part).toString();
						obj.put("number", part.getNumber());
						obj.put("state", state);
						obj.put("view", part.getViewName());
						obj.put("version", version);
						array.put(obj);
					}
				}

			}
		} catch (Exception e) {

		} finally {
			try {
				object.put("tmCode", BOMHelp.correctcode);
				object.put("datalist", array);
				object.put("length", list.size());
			} catch (JSONException e) {
				log.info("ext.tianma.bom.eco.ECOHelp.queryECOHistory error" + e);
			}

		}

		return object.toString();

	}

	/**
	 * 
	 * @param econumber
	 *            ECO编号
	 * @param number
	 *            受影响对象编号
	 * @param operatetype
	 *            变更类型
	 * @param types
	 *            单个变更、多个变更
	 * @return
	 */
	public static String queryECOOrderbyType(String oid, String econumber, String number, String operatetype,
			String types, String view) {
		JSONObject object = new JSONObject();
		JSONArray array = new JSONArray();
		List<BOMBean> list = new ArrayList<BOMBean>();
		WTChangeOrder2 eco;
		try {
			if (!BOMHelp.isNullOrEmpty(oid)) {
				eco = (WTChangeOrder2) BOMHelp.REF_FACTORY.getReference(oid).getObject();
				if (eco != null) {
					list = ECOCRUDUtil.queryECOByNumberAndType(eco.getNumber(), number, operatetype, types, view);
				}
			} else if (!BOMHelp.isNullOrEmpty(econumber)) {
				list = ECOCRUDUtil.queryECOByNumberAndType(econumber, number, operatetype, types, view);
			}

		} catch (WTException e1) {
			e1.printStackTrace();
		}

		for (BOMBean bean : list) {
			try {

				if (BOMBean.ADDPart.equals(bean.getType())) {
					JSONObject obj = new JSONObject();
					obj.put("newaddnum", bean.getOldpartnumber());
					obj.put("amount", bean.getOldpartamount());
					obj.put("effectnumber", bean.getEffectnumber());
					obj.put("view", bean.getView());
					obj.put("type", BOMBean.ADDPart);

					// 受影响对象
					WTPart effectpart = null;
					// 新增物料
					WTPart part = null;
					try {
						effectpart = GenericUtil.getPart(bean.getEffectnumber(), "", bean.getView());
						part = GenericUtil.getPart(bean.getOldpartnumber(), "", bean.getView());
					} catch (Exception e) {
						e.printStackTrace();
					}
					if (effectpart != null) {
						obj.put("effectstate", effectpart.getState().getState().getDisplay(Locale.CHINA));
					}
					if (part != null) {
						obj.put("state", part.getState().getState().getDisplay(Locale.CHINA));
						obj.put("unit", part.getDefaultUnit());
					}

					array.put(obj);

				} else if (BOMBean.DelPart.equals(bean.getType())) {
					JSONObject obj = new JSONObject();
					obj.put("partnumber", bean.getOldpartnumber());
					obj.put("linenumber", bean.getOldpartlinenumber());
					obj.put("amount", bean.getOldpartamount());
					obj.put("effectnumber", bean.getEffectnumber());
					obj.put("view", bean.getView());
					obj.put("type", BOMBean.DelPart);

					// 受影响对象
					WTPart effectpart = null;
					// 删除物料
					WTPart part = null;
					try {
						effectpart = GenericUtil.getPart(bean.getEffectnumber(), "", bean.getView());
						part = GenericUtil.getPart(bean.getOldpartnumber(), "", bean.getView());
					} catch (Exception e) {
						e.printStackTrace();
					}
					if (effectpart != null) {
						obj.put("effectstate", effectpart.getState().getState().getDisplay(Locale.CHINA));
					}
					if (part != null) {
						obj.put("state", part.getState().getState().getDisplay(Locale.CHINA));
						obj.put("unit", part.getDefaultUnit());
					}

					array.put(obj);

				} else if (BOMBean.ReplacePart.equals(bean.getType())) {
					JSONObject obj = new JSONObject();
					obj.put("oldnumber", bean.getOldpartnumber());
					obj.put("oldamount", bean.getOldpartamount());
					obj.put("oldlinenumber", bean.getOldpartlinenumber());
					obj.put("newnumber", bean.getNewpartnumber());
					obj.put("newamount", bean.getNewpartamount());
					obj.put("newlinenumber", bean.getNewpartlinenumber() == null ? bean.getOldpartlinenumber()
							: bean.getNewpartlinenumber());
					obj.put("effectnumber", bean.getEffectnumber());
					obj.put("view", bean.getView());
					obj.put("type", BOMBean.ReplacePart);

					// 受影响对象
					WTPart effectpart = null;
					// 原料
					WTPart oldpart = null;
					// 新料
					WTPart newpart = null;
					try {
						effectpart = GenericUtil.getPart(bean.getEffectnumber(), "", bean.getView());
						oldpart = GenericUtil.getPart(bean.getOldpartnumber(), "", bean.getView());
						newpart = GenericUtil.getPart(bean.getNewpartnumber(), "", bean.getView());
					} catch (Exception e) {
						e.printStackTrace();
					}
					if (effectpart != null) {
						obj.put("effectstate", effectpart.getState().getState().getDisplay(Locale.CHINA));
					}
					if (oldpart != null) {
						obj.put("oldstate", oldpart.getState().getState().getDisplay(Locale.CHINA));
						obj.put("oldunit", oldpart.getDefaultUnit());
					}
					if (newpart != null) {
						obj.put("newstate", newpart.getState().getState().getDisplay(Locale.CHINA));
						obj.put("newunit", newpart.getDefaultUnit());
					}

					array.put(obj);
				} else if (BOMBean.UpdatePart.equals(bean.getType())) {
					JSONObject obj = new JSONObject();
					obj.put("oldnumber", bean.getOldpartnumber());
					obj.put("oldamount", bean.getOldpartamount());
					obj.put("oldlinenumber", bean.getOldpartlinenumber());
					obj.put("newamount", bean.getNewpartamount());
					obj.put("effectnumber", bean.getEffectnumber());
					obj.put("view", bean.getView());
					obj.put("type", BOMBean.UpdatePart);

					// 受影响对象
					WTPart effectpart = null;
					// 原料
					WTPart oldpart = null;
					try {
						effectpart = GenericUtil.getPart(bean.getEffectnumber(), "", bean.getView());
						oldpart = GenericUtil.getPart(bean.getOldpartnumber(), "", bean.getView());
					} catch (Exception e) {
						e.printStackTrace();
					}
					if (effectpart != null) {
						obj.put("effectstate", effectpart.getState().getState().getDisplay(Locale.CHINA));
					}
					if (oldpart != null) {
						obj.put("oldstate", oldpart.getState().getState().getDisplay(Locale.CHINA));
						obj.put("oldunit", oldpart.getDefaultUnit());
					}

					array.put(obj);
				} else if (BOMBean.ADDReplcePart.equals(bean.getType())) {
					JSONObject obj = new JSONObject();
					obj.put("numberA", bean.getPartAnumber());
					obj.put("linenumberA", bean.getPartAlinenumber());
					obj.put("numberB", bean.getPartBnumber());
					obj.put("amountB", bean.getPartBamount());
					obj.put("effectnumber", bean.getEffectnumber());
					obj.put("view", bean.getView());
					obj.put("type", BOMBean.ADDReplcePart);

					// 受影响对象
					WTPart effectpart = null;
					// 主料
					WTPart partA = null;
					// 新增替代料
					WTPart partB = null;
					try {
						effectpart = GenericUtil.getPart(bean.getEffectnumber(), "", bean.getView());
						partA = GenericUtil.getPart(bean.getPartAnumber(), "", bean.getView());
						partB = GenericUtil.getPart(bean.getPartBnumber(), "", bean.getView());
					} catch (Exception e) {
						e.printStackTrace();
					}
					if (effectpart != null) {
						obj.put("effectstate", effectpart.getState().getState().getDisplay(Locale.CHINA));
					}
					if (partA != null) {
						obj.put("stateA", partA.getState().getState().getDisplay(Locale.CHINA));
						obj.put("unitA", partA.getDefaultUnit());
					}
					if (partB != null) {
						obj.put("stateB", partB.getState().getState().getDisplay(Locale.CHINA));
						obj.put("unitB", partB.getDefaultUnit());
					}

					array.put(obj);
				} else if (BOMBean.DelReplcePart.equals(bean.getType())) {
					JSONObject obj = new JSONObject();
					obj.put("numberA", bean.getPartAnumber());
					obj.put("linenumberA", bean.getPartAlinenumber());
					obj.put("numberB", bean.getPartBnumber());
					obj.put("effectnumber", bean.getEffectnumber());
					obj.put("view", bean.getView());
					obj.put("type", BOMBean.DelReplcePart);

					// 受影响对象
					WTPart effectpart = null;
					// 主料
					WTPart partA = null;
					// 删除替代料
					WTPart partB = null;
					try {
						effectpart = GenericUtil.getPart(bean.getEffectnumber(), "", bean.getView());
						partA = GenericUtil.getPart(bean.getPartAnumber(), "", bean.getView());
						partB = GenericUtil.getPart(bean.getPartBnumber(), "", bean.getView());
					} catch (Exception e) {
						e.printStackTrace();
					}
					if (effectpart != null) {
						obj.put("effectstate", effectpart.getState().getState().getDisplay(Locale.CHINA));
					}
					if (partA != null) {
						obj.put("stateA", partA.getState().getState().getDisplay(Locale.CHINA));
						obj.put("unitA", partA.getDefaultUnit());
					}
					if (partB != null) {
						obj.put("stateB", partB.getState().getState().getDisplay(Locale.CHINA));
						obj.put("unitB", partB.getDefaultUnit());
					}

					array.put(obj);
				} else if (BOMBean.UpdateReplcePart.equals(bean.getType())) {
					JSONObject obj = new JSONObject();
					obj.put("partAnumber", bean.getPartAnumber());
					obj.put("partAlinenumber", bean.getPartAlinenumber());
					obj.put("oldpartnumber", bean.getOldpartnumber());
					obj.put("oldpartamount", bean.getOldpartamount());
					obj.put("newpartnumber", bean.getNewpartnumber());
					obj.put("newpartamount", bean.getNewpartamount());
					obj.put("effectnumber", bean.getEffectnumber());
					obj.put("view", bean.getView());
					obj.put("type", BOMBean.UpdateReplcePart);

					// 受影响对象
					WTPart effectpart = null;
					// 主料
					WTPart part = null;
					// 原替代料
					WTPart oldpart = null;
					// 新替代料
					WTPart newpart = null;
					try {
						effectpart = GenericUtil.getPart(bean.getEffectnumber(), "", bean.getView());
						part = GenericUtil.getPart(bean.getPartAnumber(), "", bean.getView());
						oldpart = GenericUtil.getPart(bean.getOldpartnumber(), "", bean.getView());
						newpart = GenericUtil.getPart(bean.getNewpartnumber(), "", bean.getView());
					} catch (Exception e) {
						e.printStackTrace();
					}
					if (effectpart != null) {
						obj.put("effectstate", effectpart.getState().getState().getDisplay(Locale.CHINA));
					}
					if (part != null) {
						obj.put("partAstate", part.getState().getState().getDisplay(Locale.CHINA));
					}
					if (oldpart != null) {
						obj.put("oldstate", oldpart.getState().getState().getDisplay(Locale.CHINA));
						obj.put("oldunit", oldpart.getDefaultUnit());
					}
					if (newpart != null) {
						obj.put("newstate", newpart.getState().getState().getDisplay(Locale.CHINA));
						obj.put("newunit", newpart.getDefaultUnit());
					}

					array.put(obj);
				} else if (BOMBean.UpdateAPart.equals(bean.getType())) {
					JSONObject obj = new JSONObject();
					obj.put("oldpartnumber", bean.getOldpartnumber());
					obj.put("oldpartamount", bean.getOldpartamount());
					obj.put("oldpartlinenumber", bean.getOldpartlinenumber());
					obj.put("newpartnumber", bean.getNewpartnumber());
					obj.put("newpartamount", bean.getNewpartamount());
					obj.put("effectnumber", bean.getEffectnumber());
					obj.put("view", bean.getView());
					obj.put("type", BOMBean.UpdateAPart);

					// 受影响对象
					WTPart effectpart = null;
					// 主料
					WTPart oldpart = null;
					// 替代料
					WTPart newpart = null;
					try {
						effectpart = GenericUtil.getPart(bean.getEffectnumber(), "", bean.getView());
						oldpart = GenericUtil.getPart(bean.getOldpartnumber(), "", bean.getView());
						newpart = GenericUtil.getPart(bean.getNewpartnumber(), "", bean.getView());
					} catch (Exception e) {
						e.printStackTrace();
					}
					if (effectpart != null) {
						obj.put("effectstate", effectpart.getState().getState().getDisplay(Locale.CHINA));
					}
					if (oldpart != null) {
						obj.put("oldstate", oldpart.getState().getState().getDisplay(Locale.CHINA));
						obj.put("oldunit", oldpart.getDefaultUnit());
					}
					if (newpart != null) {
						obj.put("newstate", newpart.getState().getState().getDisplay(Locale.CHINA));
						obj.put("newunit", newpart.getDefaultUnit());
					}

					array.put(obj);

				} else if (BOMBean.ReplaceABPart.equals(bean.getType())) {
					JSONObject obj = new JSONObject();
					obj.put("partANumber", bean.getOldpartnumber());
					obj.put("partBNumber", bean.getNewpartnumber());
					obj.put("effectnumber", bean.getEffectnumber());
					obj.put("view", bean.getView());
					obj.put("type", BOMBean.ReplaceABPart);

					//行号
					if(bean.getOldpartlinenumber()!=null && !"".equals(bean.getOldpartlinenumber())
							&& !"undefined".equals(bean.getOldpartlinenumber())){
						obj.put("linenumber", bean.getOldpartlinenumber());
					}
					// 受影响对象
					WTPart effectpart = null;
					// 主料
					WTPart partA = null;
					// 替代料
					WTPart partB = null;
					try {
						effectpart = GenericUtil.getPart(bean.getEffectnumber(), "", bean.getView());
						partA = GenericUtil.getPart(bean.getOldpartnumber(), "", bean.getView());
						partB = GenericUtil.getPart(bean.getNewpartnumber(), "", bean.getView());
					} catch (Exception e) {
						e.printStackTrace();
					}
					if (effectpart != null) {
						obj.put("effectstate", effectpart.getState().getState().getDisplay(Locale.CHINA));
					}
					if (partA != null) {
						obj.put("stateA", partA.getState().getState().getDisplay(Locale.CHINA));
						obj.put("unitA", partA.getDefaultUnit());
					}
					if (partB != null) {
						obj.put("stateB", partB.getState().getState().getDisplay(Locale.CHINA));
						obj.put("unitB", partB.getDefaultUnit());
					}
					array.put(obj);
				}
				//add by xucr on 20210125 查询替代料用量修改
				else if(BOMBean.UpdateSubstitutesQuantity.equals(bean.getType())) {
					JSONObject obj = new JSONObject();
					WTPart effectPart = CoreUtil.getWTPartByNumberAndView(bean.getEffectnumber(), bean.getView());
					WTPart oldPart = CoreUtil.getWTPartByNumberAndView(bean.getSubstitutesNum(), bean.getView());
					
					obj.put("effectNumber", effectPart.getNumber());
					obj.put("effectState", effectPart.getState().getState().getDisplay(Locale.getDefault()));
					obj.put("view", bean.getView());
					obj.put("mainNum", bean.getMainNum());
					obj.put("oldNum", bean.getSubstitutesNum());
					obj.put("unit", oldPart.getDefaultUnit());
					obj.put("oldAmount", bean.getSubOldAmount());
					obj.put("oldState", oldPart.getState().getState().getDisplay(Locale.getDefault()));
					obj.put("newAmount",bean.getSubNewAmount());
					obj.put("mainLineNumber", bean.getMainLineNumber());
					array.put(obj);
				}
			} catch (JSONException e) {
				e.printStackTrace();
			} catch (WTException e) {
				e.printStackTrace();
			}
		}
		try {
			object.put("tmCode", BOMHelp.correctcode);
			object.put("datalist", array);
			object.put("length", list.size());
		} catch (JSONException e) {
			e.printStackTrace();
		}

		return object.toString();

	}

	/**
	 * 单个变更时 根据oid和受影响对象删除相应的数据
	 * 
	 * @param oid
	 * @param effectnumber
	 * @return
	 */
	public static String deleteSingleEffectData(String oid, String effectnumber, String view) {
		JSONObject object = new JSONObject();
		WTChangeOrder2 eco = null;
		String result = "";
		try {
			eco = (WTChangeOrder2) BOMHelp.REF_FACTORY.getReference(oid).getObject();
			log.info("======deleteSingleEffectData========eco:" + eco);
			if (eco != null) {
				log.info("======deleteSingleEffectData========eco:" + eco.getNumber() + "====effectnumber:"
						+ effectnumber);
				ECOCRUDUtil.deleteECOData(eco.getNumber(), effectnumber, view);
			}

		} catch (WTException e1) {
			result = e1.getMessage();
			e1.printStackTrace();
		} finally {
			try {
				object.put("tmcode", BOMHelp.correctcode);
				object.put("result", result);
			} catch (JSONException e) {
				e.printStackTrace();
			}
		}
		return object.toString();
	}

	/**
	 * 正确返回格式为： { tmCode:"000", dataList:[{
	 * number:"E001",view:"A10",state:"正在工作"}, {
	 * number:"E001",view:"A10",state:"正在工作"} ] length:2 }
	 * 
	 * @param ECO
	 *            编号
	 * @return
	 */
	public static String queryECOHistoryByNumber(String econumber) {
		JSONObject object = new JSONObject();
		JSONArray array = new JSONArray();
		List<BOMBean> list = new ArrayList<BOMBean>();
		try {
			list = ECOCRUDUtil.queryEffectPartByECONumber(econumber, "");
			for (BOMBean bean : list) {
				JSONObject obj = new JSONObject();
				WTPart part = BOMHelp.getPart(bean.getEffectnumber(), "", bean.getView());
				if (part != null) {
					String state = part.getState().getState().getDisplay(Locale.CHINA);
					String version = VersionControlHelper.getIterationDisplayIdentifier(part).toString();
					obj.put("number", part.getNumber());
					obj.put("state", state);
					obj.put("view", part.getViewName());
					obj.put("version", version);
					array.put(obj);
				}
			}

		} catch (Exception e) {

		} finally {
			try {
				object.put("tmCode", BOMHelp.correctcode);
				object.put("datalist", array);
				object.put("length", list.size());
			} catch (JSONException e) {
				log.info("ext.tianma.bom.eco.ECOHelp.queryECOHistory error" + e);
			}

		}

		return object.toString();

	}

	/**
	 * 自动生成编号
	 * 
	 * @return
	 * @throws WTException
	 */
	public static String getBOMAutoLineNumber() throws WTException {
		String strNumber = PersistenceHelper.manager.getNextSequence("TMECOITEM_seq");
		log.info("TMECOITEM_seq......................:" + strNumber);
		while (strNumber.length() < 10) {
			strNumber = "0" + strNumber;
		}
		strNumber = "TMECOITEM_" + strNumber;
		log.info("return.....................:" + strNumber);
		return strNumber;
	}

	public static String exportECOReport(JSONArray array) {
		JSONObject obj = new JSONObject();
		String downloadurl = "";
		String message = "";
		try {
			downloadurl = ecportExcel(array);
			obj.put("downloadurl", downloadurl);
			obj.put("result", message);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			log.info("=====exportECOReport Exception:"+e);
			e.printStackTrace();
		}
		return obj.toString();

	}

	public static String ecportExcel(JSONArray array) throws IOException  {
		// 遍历数据
		JSONArray resultarray = new JSONArray();
		// 新增物料
		JSONArray addarray = new JSONArray();
		// 删除物料
		JSONArray delarray = new JSONArray();
		// 替换物料
		JSONArray relarray = new JSONArray();
		// 用量修改
		JSONArray updatearray = new JSONArray();
		// 新增替代料
		JSONArray addrarray = new JSONArray();
		// 删除替代料
		JSONArray delrarray = new JSONArray();
		// 更换替代料
		JSONArray updaterelarray = new JSONArray();
		// 更换主料
		JSONArray repAarray = new JSONArray();
		// 主次替换
		JSONArray repABarray = new JSONArray();

		// 先分组
		for (int i = 0; i < array.length(); i++) {
			try {
				JSONObject object = (JSONObject) array.get(i);
				String type = "";
				if (object.has("type")) {
					type = object.getString("type");
					log.info("=====ecportExcel type:"+type);
					if (BOMBean.ADDPart.equals(type)) {
						addarray.put(object);
					} else if (BOMBean.DelPart.equals(type)) {
						delarray.put(object);
					} else if (BOMBean.ReplacePart.equals(type)) {
						relarray.put(object);
					} else if (BOMBean.UpdatePart.equals(type)) {
						updatearray.put(object);
					} else if (BOMBean.ADDReplcePart.equals(type)) {
						addrarray.put(object);
					} else if (BOMBean.DelReplcePart.equals(type)) {
						delrarray.put(object);
					} else if (BOMBean.UpdateReplcePart.equals(type)) {
						updaterelarray.put(object);
					} else if (BOMBean.UpdateAPart.equals(type)) {
						repAarray.put(object);
					} else if (BOMBean.ReplaceABPart.equals(type)) {
						repABarray.put(object);
					}
				}
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		// 初始化 Excel sheet及表头
		String[] addtitle = { "序号", "新增料号", "状态", "用量", "单位", "受影响对象" };
		String[] deltitle = { "序号", "删除料号", "状态", "用量", "单位", "行号", "受影响对象" };
		String[] reltitle = { "序号", "原料号", "原料状态", "原料号用量", "原料单位", "行号", "新料号", "新料状态", "新料号用量", "新料单位", "变更后行号	","受影响对象" };
		String[] updatetitle = { "序号", "变更料号", "变更料状态", "变更前用量", "变更料单位", "行号", "变更后用量", "单位", "受影响对象" };
		String[] addrtitle = { "序号", "主料号", "主料状态", "行号", "新增代替料料号", "替代料状态", "新增代替料用量", "单位", "受影响对象" };
		String[] delatitle = { "序号", "主料号", "主料状态", "行号", "删除替代料料号", "替代料状态", "受影响对象" };
		String[] updateretitle = { "序号", "主料号", "主料状态", "行号", "原替代料料号	", "原替代料状态", "变更前用量", "原替代料单位", "新替代料料号",
				"新替代料状态", "变更后用量", "新替代料单位", "受影响对象" };
		String[] repAtitle = { "序号", "原主料料号", "原主料状态", "行号", "变更前用量", "原主料单位", "新主料料号", "新主料状态", "变更后用量", "新主料单位",
				"受影响对象" };
		String[] repABtitle = { "序号", "原主料料号", "原主料状态", "原主料行号", "原代替料料号", "原代替料状态", "受影响对象" };

		String[] sheetNames = { "新增物料", "删除物料", "替换物料", "用量修改", "新增替代料", "删除替代料", "更换替代料", "更换主料", "主次替换" };

		PropertiesUtil ut = new PropertiesUtil(configFilePath);
		String domain = ut.getValueByKey("system.domain");
		String folder = "TempExcel";
		WTProperties props = WTProperties.getLocalProperties();
		String filePath = props.getProperty("wt.codebase.location") + File.separator + "temp" + File.separator + folder
				+ File.separator;
		File targerFile = new File(filePath);
		if (!targerFile.exists()) {
			targerFile.mkdir();
		}

		String pathAndName = filePath + "ECOChangeReport.xls";
		 String realPath = domain + File.separator + "temp" + File.separator +
		 folder + File.separator + "ECOChangeReport.xls";

		//String realPath = "C:\\Users\\Administrator\\Desktop\\ECO变更报表.xls";

		// 1.创建HSSFWorkbook,对应一个excel

		HSSFWorkbook wb = new HSSFWorkbook();

		// 2.在workbook中创建一个sheet，对应excel中的sheet

		for (String sheetName : sheetNames) {
			log.info("=====ecportExcel sheetName:"+sheetName);
			String[] title = addtitle;
			if ("新增物料".equals(sheetName)) {
				title = addtitle;
				resultarray = addarray;
			} else if ("删除物料".equals(sheetName)) {
				title = deltitle;
				resultarray = delarray;
			} else if ("替换物料".equals(sheetName)) {
				title = reltitle;
				resultarray = relarray;
			} else if ("用量修改".equals(sheetName)) {
				title = updatetitle;
				resultarray = updatearray;
			} else if ("新增替代料".equals(sheetName)) {
				title = addrtitle;
				resultarray = addrarray;
			} else if ("删除替代料".equals(sheetName)) {
				title = delatitle;
				resultarray = delrarray;
			} else if ("更换替代料".equals(sheetName)) {
				title = updateretitle;
				resultarray = updaterelarray;
			} else if ("更换主料".equals(sheetName)) {
				title = repAtitle;
				resultarray = repAarray;
			} else if ("主次替换".equals(sheetName)) {
				title = repABtitle;
				resultarray = repABarray;
			}
			if(resultarray.length()==0){
				continue;
			}
			HSSFSheet sheet = wb.createSheet(sheetName);
			// 3.在sheet中添加表头第0行
			HSSFRow row = sheet.createRow(0);

			// 4.创建单元格，并设置值表头 设置表头居中
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置居中

			// 声明列对象
			HSSFCell cell = null;

			// 创建标题
			for (int i = 0; i < title.length; i++) {
				cell = row.createCell(i);
				cell.setCellValue(title[i]);
				cell.setCellStyle(cellStyle);
			}

			// 创建内容

			for (int i = 0; i < resultarray.length(); i++) {
				HSSFRow tempRow = sheet.createRow(i + 1);
				JSONObject jsonObject;
				try {
					jsonObject = resultarray.getJSONObject(i);
					for (int j = 0; j < jsonObject.length() + 1; j++) {
						if(row == null){
							break;
						}
						//log.info("===row.getCell(j)==="+j+":"+row.getCell(j)==null?"":row.getCell(j).getStringCellValue());
						if(row.getCell(j) == null){
							break;
						}
						String tempCellName = row.getCell(j).getStringCellValue();
						String tempValue = "";
						
						tempValue = initvalue(tempCellName==null?"":tempCellName.trim(), jsonObject, sheetName, i);
						log.info("==ecportExcel tempValue:"+tempValue);
						tempRow.createCell(j).setCellValue(tempValue);
					}
				} catch (JSONException e) {
					// TODO Auto-generated catch block
					log.info("==ecportExcel JSONException 1:"+e);
					e.printStackTrace();
				}
				
			}
			/*// 调整每一列宽度自适应
			sheet.autoSizeColumn((short) 0);
			sheet.autoSizeColumn((short) 1);
			sheet.autoSizeColumn((short) 2);
			sheet.autoSizeColumn((short) 3);
			sheet.autoSizeColumn((short) 4);
			sheet.autoSizeColumn((short) 5);
			sheet.autoSizeColumn((short) 6);
			sheet.autoSizeColumn((short) 7);
			sheet.autoSizeColumn((short) 8);
			sheet.autoSizeColumn((short) 9);
			sheet.autoSizeColumn((short) 10);
			sheet.autoSizeColumn((short) 11);
			sheet.autoSizeColumn((short) 12);*/
		}

		FileOutputStream os;
		try {
			os = new FileOutputStream(pathAndName);
			wb.write(os);
			os.flush();
			os.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			log.info("==ecportExcel FileNotFoundException 1:"+e);
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			log.info("==ecportExcel IOException 1:"+e);
			e.printStackTrace();
		}
		
		return realPath;
	}
	/**
	 * 
	 * @param tempCellName  表头名称
	 * @param jsonObject    数据
	 * @param sheetName     sheet名称
	 * @param i		行
	 * @return
	 */
	public static String initvalue(String tempCellName, JSONObject jsonObject, String sheetName, int i) {
		String tempValue = "";
		
		try {
			
			if ("序号".equals(tempCellName)) {
				tempValue = String.valueOf(i + 1);
			}
			if ("新增物料".equals(sheetName)) {
				if ("新增料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newaddnum");
				} else if ("状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("state");
				} else if ("用量".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("amount");
				} else if ("单位".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("unit");
				} else if ("受影响对象".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("effectnumber");
				} 
			} else if ("删除物料".equals(sheetName)) {
				if ("删除料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("partnumber");
				} else if ("状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("state");
				} else if ("用量".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("amount");
				} else if ("单位".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("unit");
				} else if ("受影响对象".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("effectnumber");
				} else if ("行号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("linenumber");
				} 
			} else if ("替换物料".equals(sheetName)) {
				if ("原料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldnumber");
				} else if ("原料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldstate");
				} else if ("原料号用量".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldamount");
				} else if ("原料单位".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldunit");
				} else if ("行号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldlinenumber");
				}else if ("新料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newnumber");
				}else if ("新料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newstate");
				}else if ("新料号用量".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newamount");
				}else if ("新料单位".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newunit");
				}else if ("变更后行号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newlinenumber");
				}else if ("受影响对象".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("effectnumber");
				}
			} else if ("用量修改".equals(sheetName)) {
				if ("变更料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldnumber");
				} else if ("变更料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldstate");
				} else if ("变更前用量".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldamount");
				} else if ("变更料单位".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldunit");
				} else if ("行号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldlinenumber");
				}else if ("变更后用量".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newamount");
				}else if ("单位".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldunit");
				}else if ("受影响对象".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("effectnumber");
				}
			} else if ("新增替代料".equals(sheetName)) {
				if ("主料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("numberA");
				} else if ("主料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("stateA");
				} else if ("行号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("linenumberA");
				} else if ("新增代替料料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("numberB");
				} else if ("替代料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("stateB");
				}else if ("新增代替料用量".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("amountB");
				}else if ("单位".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("unitB");
				}else if ("受影响对象".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("effectnumber");
				}

			} else if ("删除替代料".equals(sheetName)) {
				if ("主料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("numberA");
				} else if ("主料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("stateA");
				} else if ("行号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("linenumberA");
				} else if ("删除替代料料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("numberB");
				} else if ("替代料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("stateB");
				}else if ("受影响对象".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("effectnumber");
				}
			} else if ("更换替代料".equals(sheetName)) {
				if ("主料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("partAnumber");
				} else if ("主料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("partAstate");
				} else if ("行号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("partAlinenumber");
				} else if ("原替代料料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldpartnumber");
				} else if ("原替代料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldstate");
				} else if ("变更前用量".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldpartamount");
				}else if ("原替代料单位".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldunit");
				}else if ("新替代料料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newpartnumber");
				} else if ("新替代料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newstate");
				} else if ("变更后用量".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newpartamount");
				}else if ("新替代料单位".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newunit");
				}else if ("受影响对象".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("effectnumber");
				}
			} else if ("更换主料".equals(sheetName)) {
				if ("原主料料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldpartnumber");
				} else if ("原主料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldstate");
				} else if ("行号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldpartlinenumber");
				}  else if ("变更前用量".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldpartamount");
				}else if ("原主料单位".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("oldunit");
				} else if ("新主料料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newpartnumber");
				} else if ("新主料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newstate");
				}else if ("变更后用量".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newpartamount");
				}else if ("新主料单位".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("newunit");
				} else if ("受影响对象".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("effectnumber");
				}
			} else if ("主次替换".equals(sheetName)) {
				
				if ("原主料料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("partANumber");
				} else if ("原主料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("stateA");
				} else if ("原主料行号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("linenumber");
				} else if ("原代替料料号".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("partBNumber");
				}  else if ("原代替料状态".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("stateB");
				} else if ("受影响对象".equals(tempCellName)) {
					tempValue = (String) jsonObject.get("effectnumber");
				}
			}
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tempValue;
	}

	public static void main(String[] args) {

	}
}
